package org.brian.webscrape;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Persist {

	private static final String QUERY_SQL = "select id,link,bedrooms,post_date,price from post";
	private static final String INSERT_SQL = "insert into post(link,post_date,bedrooms,price) values (? ,?, ?, ? ) ";
	private PreparedStatement insertStatement = null;
	
	Connection con;
	
	List<CLPost> posts = new ArrayList<CLPost>();
	
	public Persist() {
		try{
			this.connect();
		}catch(Exception e){
			throw new RuntimeException(e);
		}
	}
	
	private void connect() throws SQLException{
		try {
		      Class.forName("org.hsqldb.jdbcDriver" );
		  } catch (Exception e) {
		      System.err.println("ERROR: failed to load HSQLDB JDBC driver.");
		      e.printStackTrace();
		      return;
		  }
		  con = DriverManager.getConnection("jdbc:hsqldb:file:/Users/bsmith1/Desktop/testdb;ifexists=true;shutdown=true", "SA", "");
	}
	
	private void loadIntoMemory() throws SQLException{
		posts.clear();
		Statement s = con.createStatement();
		s.execute(QUERY_SQL);
		ResultSet rs = s.getResultSet();
		while(rs.next()){
			Date dt = rs.getDate("post_date");
			String url = rs.getString("link");
			Double price = rs.getDouble("price");
			Integer bedrooms = rs.getInt("bedrooms");
			//CLPost p = new CLPost(dt, url, String.valueOf(price), bedrooms, null);
			//posts.add(p);
		}
		
		System.out.println("Posts size: "+posts.size());
		
	}
	
	public List<CLPost> getAll() throws SQLException{
		if(posts.size() ==0 )
			loadIntoMemory();
		return posts;//breaks encapsulation?
	}
	
	private void disconnect() throws SQLException{
		con.close();	
	}
	
	private boolean isClosed() throws SQLException{
		return con.isClosed();
	}
	
	public void save(List<CLPost> posts){
		for(CLPost p : posts){
			save(p);
		}
	}
	
	public void save(CLPost post ){
		try{
			if(insertStatement == null){
				insertStatement = con.prepareStatement(INSERT_SQL);
			}
			insertStatement.setString(1, post.getUrl() );
			if(null != post.getDate()){
				insertStatement.setDate(2, new java.sql.Date(post.getDate().getTime()));
			}
			if(null != post.getBedrooms()){
				insertStatement.setInt(3, post.getBedrooms() );
			}
			if(null != post.getPrice()){
				insertStatement.setInt(4, post.getPrice() );
			}

			insertStatement.executeUpdate();
		}catch(Exception e){
			throw new RuntimeException (e);
		}
		
	}
	
	
	public static void main(String [] args) throws SQLException{
		long startTime = System.currentTimeMillis();
		Persist p = new Persist();

		p.loadIntoMemory();		
		
		/*
		CLPost testPost = new CLPost(new Date(), "testUrl", "123.0", 123, null);
		p.save(testPost);		
		
		CLPost testPost2 = new CLPost(new Date(), "222", "222.0", 222, null);
		p.save(testPost2);

		p.loadIntoMemory();		
		*/
		if(!p.isClosed() ) p.disconnect();		
		printWithMessage("disconnect", startTime);
	}
	
	private static void printWithMessage(String msg, long startTime){
		System.out.println(msg + " " + (System.currentTimeMillis() - startTime) + "ms");
	}
}
